	<!-- 
      create.php
      创建数据库
      
      Created by dfer on 2017-10-23.
      Copyright 2017 dfer. All rights reserved.
-->
<?php
define('INIT', 'df');
$create=true	;
require "share/config.php";

/*默认值在输入一部分内容之后会自动填充
 * 
 */

//————————————————————————————————————————创建数据库
//————————————————————核心库
//后台登陆账号，不要删
 $sql[] = "CREATE TABLE `df` 
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
nm varchar(50) DEFAULT 'df' COMMENT '账号名',
pw varchar(50) DEFAULT 'df',
pic varchar(100) DEFAULT '/favicon.png',
role int NOT NULL COMMENT '权限',
createtime datetime COMMENT '创建时间',
lastlogintime datetime COMMENT '上次访问'
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
";
//账号权限，不要删
 $sql[] = "CREATE TABLE `roles` 
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
nm varchar(50) DEFAULT '普通用户' COMMENT '权限名',
roles varchar(100) DEFAULT '1|2' COMMENT '权限内容'
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
";
//常用数据，不要删
 $sql[] = "CREATE TABLE `dt` 
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
hits int DEFAULT 0 COMMENT '用户访问量'
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
";

//后台菜单，不要删
 $sql[]= "CREATE TABLE menu
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
title varchar(50) COMMENT '标题',
src varchar(100) COMMENT '路径',
type varchar(30) COMMENT '类型',
parent int DEFAULT 0 COMMENT '上级Id',
orderNum int DEFAULT 0 COMMENT '排序编号'
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
";
//html页面，不要删
 $sql[]= "CREATE TABLE html
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
fileN varchar(50) COMMENT 'htm文件名',
src varchar(100) COMMENT '动态路径',
comment varchar(30) COMMENT '备注'
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
";
//服务器缓存,不要删
 $sql[]="CREATE TABLE cache
(
 `key` varchar(50) NOT NULL,
  `value` longtext NOT NULL,
  PRIMARY KEY (`key`)
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";

//日志，不要删
 $sql[]= "CREATE TABLE logs
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
str longtext COMMENT '记录内容',
time datetime
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
";
//————————————————————基础库

//主页布局
 $sql[]="CREATE TABLE home_layout 
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
title varchar(100) DEFAULT '' COMMENT '',
keywords varchar(100) DEFAULT '' COMMENT '关键字',
description varchar(100) DEFAULT '' COMMENT '网页简介',
Inscribe varchar(100) DEFAULT '' COMMENT '',
img1 varchar(100) DEFAULT '' COMMENT '背景图像',
color varchar(10) DEFAULT '' COMMENT '主体字体颜色',
musicPlay tinyint DEFAULT 0 COMMENT '音乐自动播放',
`sceneId` int DEFAULT 0 COMMENT '模板id'
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";

//背景图片列表
$sql[]="CREATE TABLE home_layout_img 
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
title varchar(100) DEFAULT '' COMMENT '',
img varchar(100) DEFAULT '' COMMENT '背景图像'
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";


//栏目
 $sql[]="CREATE TABLE home_column
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
menu varchar(100) DEFAULT '' COMMENT '',
title varchar(100) DEFAULT '' COMMENT '',
`describe` varchar(100) DEFAULT '' COMMENT '',
content longtext DEFAULT '' COMMENT '内容'
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";


//链接
 $sql[]="CREATE TABLE home_link
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
title varchar(100) DEFAULT '' COMMENT '',
`src` varchar(100) DEFAULT '' COMMENT ''
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";

//音乐
 $sql[]="CREATE TABLE home_music
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
title varchar(100) DEFAULT '' COMMENT '',
`src` varchar(100) DEFAULT '' COMMENT ''
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";


//留言
 $sql[]="CREATE TABLE message
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
name varchar(100) DEFAULT '' COMMENT '',
E_mail varchar(100) DEFAULT '' COMMENT '',
`content` longtext DEFAULT '' COMMENT '内容',
time varchar(50)
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";

//记事本
 $sql[]= "CREATE TABLE notepad
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
title varchar(50) COMMENT '标题',
content longtext COMMENT '内容',
time varchar(50)
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
";

//站点介绍	
 $sql[] = "CREATE TABLE `column`
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
menu varchar(50),
title varchar(55),
pic varchar(100),
content longtext
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
";



//————————————————————拓展库

//用户
$sql[]="CREATE TABLE user
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
nickname varchar(50) DEFAULT '' COMMENT '',
sex varchar(5) DEFAULT '' COMMENT '性别',
QQ_pic varchar(100) DEFAULT '' COMMENT 'qq头像',
Qzone_pic varchar(100) DEFAULT '' COMMENT '空间头像',
addr varchar(100) DEFAULT '' COMMENT '地址',
years int default 0 COMMENT '出生年',
coin decimal(10,2) default 0 COMMENT '资金（最大值为九千多万）',
points decimal(10,2) default 0 COMMENT '积分（最大值为九千多万）',
subs longtext COMMENT '信息',
openid varchar(100) DEFAULT '' COMMENT '',
IP varchar(33) COMMENT '最近登陆使用的ip',
lastlogintime datetime COMMENT '最近登陆时间',
createtime datetime COMMENT '注册时间'
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";



//帖子
$sql[]="CREATE TABLE tie
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
title varchar(55) not null COMMENT '标题',
msg longtext COMMENT '内容',
userId int not null COMMENT '发送者id',
time datetime COMMENT '发送时间'
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";

//评论
$sql[]="CREATE TABLE comment
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
PageId int not null COMMENT '文章id',
userId int not null COMMENT '发送者id',
ReceiveId int default 0 COMMENT '接受信息者的id',
rowId int default 0 COMMENT '评论的行id',
msg longtext COMMENT '信息',
time datetime COMMENT '发送时间'
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";




//商品
$sql[]="CREATE TABLE sale_code
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
title varchar(100) DEFAULT '' COMMENT '',
`content` longtext DEFAULT '' COMMENT '内容',
file varchar(100) DEFAULT '' COMMENT '',
userId int not null COMMENT '作者id',
price int default 0,
viewCount int default 0 COMMENT '浏览量',
downloadCount int default 0 COMMENT '下载量',
time datetime
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";

//zfb-支付记录
 $sql[] = "CREATE TABLE zfb_order 
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
out_trade_no varchar(100) COMMENT '商户订单号',
trade_no varchar(100) COMMENT '支付宝交易号',
trade_status varchar(100) COMMENT '交易状态',
subject varchar(100) COMMENT '标题',
body varchar(100) COMMENT '描述',
receipt_amount decimal(50,2) COMMENT '交易金额',
time datetime
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";



//用户-充值记录
 $sql[] = "CREATE TABLE paylog 
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
title varchar(50) COMMENT '标题',
trade_no varchar(100) COMMENT '支付宝交易号',
money decimal(50,2) COMMENT '充值金额',
ye decimal(50,2) COMMENT '余额',
userId int COMMENT '用户id',
time datetime
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";

//付费状态
//判断用户是否已购买产品
$sql[]="CREATE TABLE pay_state
(
Id int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(Id),
productId int DEFAULT 0 COMMENT '购买的产品的id',
openid varchar(100) DEFAULT '' COMMENT '用户标识',
userId int DEFAULT 0 COMMENT '用户id',
state int default 0 COMMENT '购买状态|||0或不存在:未购买；1：已购买',
price decimal(10,2) default 0 COMMENT '购买价格',
zk decimal(10,2) default 0 COMMENT '折扣比率',
time datetime COMMENT '购买时间'
)DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";

//数据库不存在就创建数据库
if(!mysqli_select_db($con, $database))
if (mysqli_query($con,"CREATE DATABASE {$database}"))
  {
  echo("Database {$database} created successfully ");
 	header('location:create.php');//先创建库，然后自动刷新，创建表
 	die();
  }
else
  {
  echo "{$database} created err: " . mysqli_error($con);
  }
echo "<br>";

//创建表
$num=0;
foreach ($sql as $val) {
	$num++;
	echo $num.".";
	$str=explode("(",$val)[0];  
   if ($db->query($val))  
   echo $str." successfully";
   else 
   echo $str." err: " . $db->error;   
   echo "<br>";
}


//————————————————————————————————————————添加初始数据
//添加登陆账号
//echo $db->query("SELECT * FROM `df`")->fetch_array()[1];   //读取首条数据
if($db->query("SELECT COUNT(*) AS count FROM `df`")->fetch_array()[0]<1)
if($db->query("insert into `df`(nm,pw,pic) values('df','df','http://www.dfer.top/favicon.png')"))
echo "Add df user successfully";
else
echo "Add df user fail";
else
echo "df user exist";
echo '<br>';
//添加账号权限
if($db->query("SELECT COUNT(*) AS count FROM `roles`")->fetch_array()[0]<1)
if($db->query("insert into `roles`(nm,roles) values('超级用户',''),('普通用户','1|2|7|10|15|16|')"))
echo "Add roles successfully";
else
echo "Add roles fail";
else
echo "roles exist";
echo '<br>';
//添加通用参数
if($db->query("SELECT COUNT(*) AS count FROM `dt`")->fetch_array()[0]<1)
if($db->query("insert into `dt`(hits) values(0)"))
echo "Add dt user successfully";
else
echo "Add dt user fail";
else
echo "dt exist";
echo '<br>';
//添加静态页面
if($db->query("SELECT COUNT(*) AS count FROM `html`")->fetch_array()[0]<1)
if($db->query("insert into `html` (`fileN`,`src`) values('index','homepage/home/')"))
echo "Add dt html successfully";
else
echo "Add dt html fail";
else
echo "html exist";
echo '<br>';	
	
//添加基础菜单
if($db->query("SELECT COUNT(*) AS count FROM `menu`")->fetch_array()[0]<1)
if($db->query("INSERT INTO `menu` (`title`, `src`, `type`, `parent`, `orderNum`) VALUES
('动态首页', 'homepage%2Fhome%2F', 'home', 0, 0),
('主页管理', '', 'folder', 0, 1),
('用户管理', '', 'user', 0, 2),
('生成静态页面', '', 'folder', 0, 8888),
('用户', '', 'folder', 0, 0),
('支付宝', '', 'home', 0, 0),
('商品', 'product%2Flist%2Fcolumn', 'home', 0, 0),
('记事本', 'admin%2Fhome%2Fnotepad', 'book', 0, 100),
('关于此站点', 'admin%2Fhome%2Fcolumn	', 'info', 0, 110),
('刷新数据', 'js%3Arefresh_data%28%29%3B', 'refresh', 0, 120),
('装载数据', 'url%3A%2Fcreate.php', 'save', 0, 130),
('框架信息', 'admin%2Fhome%2Finfo', 'info', 0, 140),
('菜单', 'admin%2Fhome%2Fmenu', 'lock', 0, 150),
('日志', 'admin%2Fhome%2Flog', 'history', 0, 160),
('使用说明', 'admin%2Fhome%2Freadme', 'bug', 0, 170),

('布局', 'homepage%2Fcolumn%2Fhome_layout%2F1', 'file', 2, 0),
('栏目管理', 'homepage%2Fcolumn%2Fhome_column', 'file', 2, 0),
('链接管理', 'homepage%2Fcolumn%2Fhome_link', 'link', 2, 0),
('音乐管理', 'homepage%2Fcolumn%2Fhome_music', 'music', 2, 0),
('留言管理', 'homepage%2Fcolumn%2Fmessage+', 'comments', 2, 0),

('列表', 'admin%2Fhome%2Fdf', 'file', 3, 0),
('权限', 'admin%2Fhome%2Froles', 'file', 3, 0),

('查看字体', 'url%3A%2Fhtm%2Ffont.html', 'file', 4, 8880),
('页面管理', 'admin%2Fhome%2Fhtml', 'file', 4, 8881),
('生成', 'admin%2Fhome%2FcreateStaticPage', 'file', 4, 8882),

('列表', 'user%2Fmain%2Fuser', 'users', 5, 0),
('充值记录', 'user%2Fmain%2Fpaylog', 'home', 5, 0),
('购买记录', 'user%2Fmain%2Fbuylog', 'home', 5, 0),

('主页', 'zfb%2Fpay%2Findex', 'home', 6, 0),
('记录', 'zfb%2Fpay%2FpayLog', 'home', 6, 0),
('打赏', 'zfb%2Fpay%2FdsV', 'home', 6, 0),
('回调', 'zfb%2Fpay%2Freturn_url', 'home', 6, 0)
;"))


echo "Add df menu successfully";
else
echo "Add df menu fail";
else
echo "df menu exist";
echo '<br>';	


//数据结构更新
$sql_update="ALTER TABLE `comment` ADD rowId int default 0 COMMENT '评论的行id'";
if(!empty($sql_update))
if($db->query($sql_update))
echo "sql update successfully";
else
echo "sql update fail";
else
echo "sql update nothing";
echo '<br>';	
	
$db->close();
   
   
echo("<br> <a target='_blank' href='/'>进入主页</a><br> <a href='javascript:location.reload()'>刷新...</a>");


   
?>